package edu.mis.modules.kmye;

import java.util.Calendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.alibaba.fastjson.JSONArray;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

import edu.mis.model.AccountCaption;
import edu.mis.model.Kmye;

public class KmyeService {
	public String close() throws Exception{
		Calendar c = Calendar.getInstance();
		int year = c.get(Calendar.YEAR);
		int month = c.get(Calendar.MONTH) + 1;
		String date1 = year + "-" + month + "-01";
		String date2 = year + "-" + month + "-31";
		boolean success;
		String msg;
		// 判断本月是否已经已经关过账了
		String sql = "select * from kmye where date between '" + date1 + "' and '" + date2 + "' and isInit=0 limit 1";
		if (Db.find(sql).size() == 1) {
			success= false;
			msg="本月已关账，不可再关账!";
			Map<String, Object> data = new HashMap<String, Object>();
			data.put("success", success);
			data.put("msg", msg);
			JSONArray ja = new JSONArray();
			ja.add(data);
			String str=ja.toJSONString();
			return str;
		} else {
			String s="select * from voucher where date between '" + date1 + "' and '" + date2 + "' and auditor is null";
			//尚有凭证未审核
			if(Db.find(s).size()!=0){
				success= false;
				msg="本月尚有凭证未审核，不可关账!";
				Map<String, Object> data = new HashMap<String, Object>();
				data.put("success", success);
				data.put("msg", msg);
				JSONArray ja = new JSONArray();
				ja.add(data);
				String str=ja.toJSONString();
				return str;
			}
			//凭证全部审核完毕
			java.sql.Date date = new java.sql.Date(new java.util.Date().getTime());
			List<AccountCaption> captionList = AccountCaption.dao.find(
					"SELECT captionId,balanceDirection FROM account_caption where captionId like '____' ORDER BY captionId ASC");
			int len = captionList.size();
			Kmye k = null;
			for (int i = 0; i < len; i++) {
				int captionId = captionList.get(i).getCaptionId();
				double openingBalance = 0, jffs = 0, dffs = 0, ljjf = 0, ljdf = 0;
				String balanceDirection = captionList.get(i).getBalanceDirection();
				k = new Kmye();
				k.setCaptionId(captionId);
				k.setDate(date);
				k.setBalanceDirection(balanceDirection);
				// 查询上次科目期初余额，计算本次关账期初余额
				List<Kmye> last = Kmye.dao.find("SELECT openingBalance,jffs,dffs,ljjf,ljdf FROM kmye where captionId = "
						+ captionId + " order by date desc limit 1 ");
				// 如果查询为空集，则表明此科目未设置过期初，未使用过,则默认所有数值为0；否则，取出数值进行计算
				if (last.size() == 1 && month == 1) {
					openingBalance = last.get(0).getOpeningBalance();
					jffs = last.get(0).getJffs();
					dffs = last.get(0).getDffs();
				} else if (last.size() == 1) {
					openingBalance = last.get(0).getOpeningBalance();
					jffs = last.get(0).getJffs();
					dffs = last.get(0).getDffs();
					ljjf = last.get(0).getLjjf();
					ljdf = last.get(0).getLjdf();
				}
				if (balanceDirection.equals("借")) {
					// 设置本次关账的期初余额
					k.setOpeningBalance(openingBalance + jffs - dffs);
				} else {
					k.setOpeningBalance(openingBalance - jffs + dffs);
				}
				// 取出此科目本月借方发生额和贷方发生额
				List<Record> list = Db
						.find("SELECT captionId,date,sum(debitAmount),sum(creditAmount) from ( SELECT vi.*,v.date FROM voucherItem vi LEFT JOIN voucher v ON v.voucherId=vi.voucherId ) as A WHERE date between '"
								+ date1 + "' and '" + date2 + "' and captionId = " + captionId + " group by captionId");
				if (list.size() == 0) {
					jffs = 0;
					dffs = 0;
				} else {
					if(list.get(0).getDouble("sum(debitAmount)")==null){
						jffs = 0;
					}else{
						jffs = list.get(0).getDouble("sum(debitAmount)");
					}
					if(list.get(0).getDouble("sum(creditAmount)")==null){
						dffs = 0;
					}else{
						dffs = list.get(0).getDouble("sum(creditAmount)");
					}
				}
				k.setJffs(jffs);
				k.setDffs(dffs);
				k.setLjjf(jffs + ljjf);
				k.setLjdf(dffs + ljdf);
				k.setIsInit(0);
				if (k.save()) {
					//设置科目已被使用
					Db.update("update account_caption set isUse = 1 where captionId="+captionId);
					success= true;
					msg="关账成功!";
				} else {
					success= false;
					msg="关账失败!";
				}
			}
			// 设置本月所有凭证已被关账
			Db.update(
					"update voucher set isClose = 1 where date between '" + date1 + "' and '" + date2 + "'");
			success= true;
			msg="关账成功!";
			
//			if (Db.update(
//					"update voucher set isClose = 1 where date between '" + date1 + "' and '" + date2 + "'") != 0) {
//				success= true;
//				msg="关账成功!";
//			} else {
//				success= false;
//				msg="关账失败!";
//			}
//			
			Map<String, Object> data = new HashMap<String, Object>();
			data.put("success", success);
			data.put("msg", msg);
			JSONArray ja = new JSONArray();
			ja.add(data);
			String str=ja.toJSONString();
			return str;
		}
	}
}
